﻿Imports System.Data.SqlClient

Module Snapshot

    Public Sub Main(ByVal args() As String)
        'RunOne();
        'RunTwo();
        RunDmcWithTrans()
    End Sub

    Private Sub RunDmcWithTrans()
        Dim user1Connection As New SqlConnection(GetConnectionString)
        user1Connection.Open()

        Dim user2Connection As New SqlConnection(GetConnectionString)
        user2Connection.Open()

        Dim user3Connection As New SqlConnection(GetConnectionString)
        user3Connection.Open()

        Dim sqlcom As New SqlCommand("IF EXISTS " +
                                     "(SELECT * FROM sys.tables WHERE name=N'snapTest') " +
                                     "DROP TABLE snapTest", user1Connection)
        sqlcom.ExecuteNonQuery()

        Dim sqlcom1 As New SqlCommand("CREATE TABLE snapTest ([id] INT IDENTITY, col1 VARCHAR(15))", user1Connection)
        sqlcom1.ExecuteNonQuery()

        Dim sqlcom2 As New SqlCommand("INSERT INTO snapTest VALUES('Niels')", user1Connection)
        sqlcom2.ExecuteNonQuery()

        Dim user3StepZ1 As New SqlCommand("SET TRANSACTION ISOLATION " +
                                          "LEVEL SNAPSHOT " +
                                          "BEGIN TRAN " + "SELECT col1 FROM snapTest WHERE id = 1", user3Connection)
        Dim user3StepZ1Read = user3StepZ1.ExecuteReader(CommandBehavior.SingleRow)

        Dim user1Step1 As New SqlCommand("SET TRANSACTION ISOLATION " +
                                         "LEVEL SNAPSHOT " +
                                         "BEGIN TRAN " + "UPDATE snapTest " +
                                         "SET col1 = 'NewNiels' " +
                                         "WHERE id = 1 ", user1Connection)
        user1Step1.ExecuteNonQuery()

        Dim user2Step2 As New SqlCommand("SET TRANSACTION ISOLATION LEVEL SNAPSHOT " +
                                         "BEGIN TRAN " +
                                         "SELECT col1 FROM snapTest " +
                                         "WHERE id = 1", user2Connection) 'receives value 'Niels'
        user2Step2.ExecuteNonQuery()

        Dim user1Step3 As New SqlCommand("COMMIT TRAN", user1Connection)
        user1Step3.ExecuteNonQuery()

        Dim user2Step4 As New SqlCommand("SELECT col1 FROM snapTest WHERE id = 1", user1Connection) 'receives value 'Niels'
        Dim user2Step4Read = user2Step4.ExecuteReader(CommandBehavior.SingleRow)
        user2Step4Read.Read()
        Console.WriteLine(user2Step4Read("col1"))
        user2Step4Read.Close()

        Dim user2Step5 As New SqlCommand("COMMIT TRAN", user2Connection)
        user2Step5.ExecuteNonQuery()

        Dim user2Step6 As New SqlCommand("SELECT col1 FROM snapTest WHERE id = 1", user2Connection) 'receives value 'NewNiels'
        Dim user2Step6Read = user2Step6.ExecuteReader(CommandBehavior.SingleRow)
        user2Step6Read.Read()
        Console.WriteLine(user2Step6Read("col1"))
        user2Step6Read.Close()

        user3StepZ1Read.Read()
        Console.WriteLine(user3StepZ1Read("col1"))
        user3StepZ1Read.Close()

        Dim user3StepZ2 As New SqlCommand("COMMIT TRAN", user3Connection)
        user3StepZ2.ExecuteNonQuery()

        user1Connection.Dispose()
        user2Connection.Dispose()
        user3Connection.Dispose()
    End Sub

    Private Sub RunDmcWithoutTrans()
        Dim user1Connection As New SqlConnection(GetConnectionString)
        user1Connection.Open()

        Dim user2Connection As New SqlConnection(GetConnectionString)
        user2Connection.Open()

        Dim user3Connection As New SqlConnection(GetConnectionString)
        user3Connection.Open()

        Dim sqlcom As New SqlCommand("IF EXISTS " +
                                     "SELECT * FROM sys.tables WHERE name=N'snapTest' " +
                                     "DROP TABLE snapTest", user1Connection)
        sqlcom.ExecuteNonQuery()

        Dim sqlcom1 As New SqlCommand("CREATE TABLE snapTest ([id] INT IDENTITY, col1 VARCHAR(15))", user1Connection)
        sqlcom1.ExecuteNonQuery()

        Dim sqlcom2 As New SqlCommand("INSERT INTO snapTest VALUES('Niels')", user1Connection)
        sqlcom2.ExecuteNonQuery()

        Dim user3StepZ1 As New SqlCommand("SET TRANSACTION ISOLATION " +
                                          "LEVEL SNAPSHOT " +
                                          "BEGIN TRAN " + "SELECT col1 FROM snapTest WHERE id = 1", user3Connection)
        Dim user3StepZ1Read = user3StepZ1.ExecuteReader(CommandBehavior.SingleRow)

        Dim user1Step1 As New SqlCommand("UPDATE snapTest " +
                                         "SET col1 = 'NewNiels' " +
                                         "WHERE id = 1 ", user1Connection)
        user1Step1.ExecuteNonQuery()

        Dim user2Step2 As New SqlCommand("SELECT col1 FROM snapTest " +
                                         "WHERE id = 1", user2Connection) 'receives value 'Niels'
        user2Step2.ExecuteNonQuery()

        Dim user2Step4 As New SqlCommand("SELECT col1 FROM snapTest WHERE id = 1", user1Connection) 'receives value 'Niels'
        Dim user2Step4Read = user2Step4.ExecuteReader(CommandBehavior.SingleRow)
        user2Step4Read.Read()
        Console.WriteLine(user2Step4Read("col1"))
        user2Step4Read.Close()

        Dim user2Step6 As New SqlCommand("SELECT col1 FROM snapTest WHERE id = 1", user2Connection) 'receives value 'NewNiels'
        Dim user2Step6Read = user2Step6.ExecuteReader(CommandBehavior.SingleRow)
        user2Step6Read.Read()
        Console.WriteLine(user2Step6Read("col1"))
        user2Step6Read.Close()

        user3StepZ1Read.Read()
        Console.WriteLine(user3StepZ1Read("col1"))
        user3StepZ1Read.Close()

        Dim user3StepZ2 As New SqlCommand("COMMIT TRAN", user3Connection)
        user3StepZ2.ExecuteNonQuery()

        user1Connection.Dispose()
        user2Connection.Dispose()
        user3Connection.Dispose()
    End Sub

    Private Function GetConnectionString() As String
        Return "MyConnectionString"
    End Function

End Module
